package com.codeduck.poi.controller.excel;

import com.alibaba.fastjson.JSON;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.xssf.binary.XSSFBUtils;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 读取xlsx文件
 *
 * @author: <a href="mailto:codeduck@163.com">Codeduck</a>
 * @date: 2023/1/28
 */
@RestController
public class ReadDataFromExcelController {

    @GetMapping("/read/from/excel")
    public String readData() {
        // xls: 2003版本Excel文件，对象为 HSSFWorkBook（最多存储65535行数据）
        // xlsx: 2007版本Excel文件，对象为XSSFWorkbook（数据量无限制）
        ClassPathResource classPathResource = new ClassPathResource("datafiles/产品信息.xlsx");
        if (!classPathResource.exists()) {
            return "文件不存在";
        }
        List<String> res;
        try {
            File file = classPathResource.getFile();
            FileInputStream inputStream = new FileInputStream(file);
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            res = readByForLoop(workbook);
        } catch (IOException e) {
            return "文件处理异常";

        }
        return JSON.toJSONString(res);
    }

    private List<String> readByForLoop(XSSFWorkbook workbook) {
        List<String> res = new ArrayList<>();
        XSSFSheet sheet = workbook.getSheetAt(0); // xssfWorkbook.getSheet("sheet1");

        int rows = sheet.getLastRowNum();   // 总行数
        int cols = sheet.getRow(0).getLastCellNum(); // 总列数

        for (int i = 0; i <= rows; i++) {
            StringBuilder sb = new StringBuilder();
            XSSFRow row = sheet.getRow(i);
            for (int j = 0; j < cols; j++) {
                XSSFCell cell = row.getCell(j);
                if (null == cell) {
                    continue;
                }
                switch (cell.getCellType()) {
                    case STRING:
                        sb.append(cell.getStringCellValue() + "(STRING)");
                        break;
                    case NUMERIC:
                        // NUMERIC类型的数据：整数，小数，日期
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            sb.append(sdf.format(date) + "(DATE)");
                        } else {
                            sb.append(cell.getNumericCellValue() + "(NUMERIC)");
                        }
                        break;   // NUMERIC类型的数据：整数，小数，日期
                    case BOOLEAN:
                        sb.append(cell.getBooleanCellValue() + "(BOOLEAN)");
                        break;
                    case FORMULA:
                        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
                        sb.append(evaluator.evaluate(cell).getNumberValue() + "(FORMULA)");
                        break;
                    case BLANK:
                        sb.append("(BLANK)");
                        break;
                    case ERROR:
                        sb.append("##ERROR##");
                        break;
                }
                if (cols != j + 1) {
                    sb.append("\t|\t");
                }
            }
            res.add(sb.toString());
        }
        return res;
    }

    private List<String> readByWhileLoop(XSSFWorkbook workbook) {
        List<String> res = new ArrayList<>();
        XSSFSheet sheet = workbook.getSheetAt(0); // xssfWorkbook.getSheet("sheet1");

        Iterator iterator = sheet.iterator();
        while (iterator.hasNext()) {
            XSSFRow row = (XSSFRow) iterator.next();
            Iterator cellIterator = row.cellIterator();
            StringBuilder sb = new StringBuilder();

            while (cellIterator.hasNext()) {
                XSSFCell cell = (XSSFCell) cellIterator.next();
                if (null == cell) {
                    continue;
                }
                switch (cell.getCellType()) {
                    case STRING:
                        sb.append(cell.getStringCellValue() + "(STRING)");
                        break;
                    case NUMERIC:
                        // NUMERIC类型的数据：整数，小数，日期
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            sb.append(sdf.format(date) + "(DATE)");
                        } else {
                            sb.append(cell.getNumericCellValue() + "(NUMERIC)");
                        }
                        break;   // NUMERIC类型的数据：整数，小数，日期
                    case BOOLEAN:
                        sb.append(cell.getBooleanCellValue() + "(BOOLEAN)");
                        break;
                    case FORMULA:
                        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
                        sb.append(evaluator.evaluate(cell).getNumberValue() + "(FORMULA)");
                        break;
                    case BLANK:
                        sb.append("(BLANK)");
                        break;
                    case ERROR:
                        sb.append("##ERROR##");
                        break;
                }
            }
            res.add(sb.toString());
        }
        return res;
    }
}
